package sz.com.postoffice.jxyzwgcase.utils;

import com.alibaba.druid.sql.visitor.functions.If;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.IOException;

/**
 * 描述:
 *
 * @author baiyu, du
 * @date 2020/12/16 10:35
 */
public class ExcelUtils {
    /**
     * 获取一个XSSFWorkbook文件对象
     *
     * @return
     */
    public static XSSFWorkbook getExcel() {
        return new XSSFWorkbook();
    }

    /**
     * 通过文件获取一个XSSFWorkbook文件对象
     *
     * @param filePath
     * @return
     */
    public static XSSFWorkbook getExcel(String filePath) {
        File file = new File(filePath);
        if (file.exists()) {
            try {
                return new XSSFWorkbook(file);
            } catch (IOException e) {
                e.printStackTrace();
            } catch (InvalidFormatException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    /**
     * 给单元格写入一个数据,新建行样式和前面的行相同
     *
     * @param sheet
     * @param rowIndex
     * @param column
     * @param value
     */
    public static void writeCellValue(XSSFSheet sheet, int rowIndex, int column, Object value) {
        if (value == null) {
            return;
        }
        XSSFRow row1 = sheet.getRow(rowIndex);
        if (row1 == null) {
            row1 = sheet.createRow(rowIndex);
        }
        XSSFCell cell = row1.getCell(column);
        if (cell == null) {
            cell = row1.createCell(column);
        }
        cell.setCellValue(value.toString());
    }

    /**
     * 重单元格读取一个值
     *
     * @param sheet
     * @param row
     * @param column
     */
    public static String readCellValue(XSSFSheet sheet, int row, int column) {
        XSSFRow row1 = sheet.getRow(row);
        return row1.getCell(column).getStringCellValue();
    }

    public static void mergedCell(XSSFSheet sheet, int beginRow, int endRow, int beginCol, int endCol) {
        CellRangeAddress region = new CellRangeAddress(beginRow, endRow, beginCol, endCol);
        sheet.addMergedRegion(region);
    }

    /**
     * 设置自动列宽
     *
     * @param sheet 表格
     * @param size  列数
     */
    public static void autoSizeColumn(XSSFSheet sheet, int size) {
        for (int i = 0; i < size; i++) {
            // 调整每一列宽度
            sheet.autoSizeColumn((short) i);
            // 解决自动设置列宽中文失效的问题
            int colWidth = sheet.getColumnWidth(i)*2;
            sheet.setColumnWidth(i, Math.min(colWidth, 6000));
        }
    }
}
